/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package State;

import Country.CountryBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(StateBean sb) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into state values(stateid.nextval,?,?,sysdate)");
            pre.setInt(1, sb.getCountryId());
            pre.setString(2, sb.getStateName());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(state_id) from state");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);;
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static StateBean viewSpe(String cname, int val) throws SQLException {
       
StateBean sb = new StateBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from state where " + cname + "=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                
                sb.setStateId(rs.getInt(1));
                sb.setCountryId(rs.getInt(2));
                sb.setStateName(rs.getString(3));
                sb.setEntryDate(rs.getDate(4));
                
                
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return sb;
    }
public static Vector viewSpecific(String cname, int val) throws SQLException {
       Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from state where " + cname + "=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                StateBean sb = new StateBean();
                sb.setStateId(rs.getInt(1));
                sb.setCountryId(rs.getInt(2));
                sb.setStateName(rs.getString(3));
                sb.setEntryDate(rs.getDate(4));
                list.add(sb);
                
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from state left join country on country.country_id=state.country_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                StateBean sb = new StateBean();
                CountryBean cb = new CountryBean();
                sb.setStateId(rs.getInt(1));
                sb.setCountryId(rs.getInt(2));
                sb.setStateName(rs.getString(3));
                sb.setEntryDate(rs.getDate(4));
                cb.setCountryId(rs.getInt(5));
                cb.setCountryName(rs.getString(6));
                sb.setCountryBean(cb);
                list.add(sb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static int edit(StateBean sb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update state set country_id=?, state_name=?,entrydate=sysdate where state_id=?");
            pre.setInt(1, sb.getCountryId());
            pre.setString(2, sb.getStateName());
            pre.setInt(3, sb.getStateId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from state where state_id=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }
}
